﻿using IService;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Models.MiniuiModes;
using Models.UserModels;
using RepositoryEF;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using ViewModels;
namespace Service
{
    public class UserService : IUserService
    {
        private readonly AppDbContext _context;
        private readonly ILogger<UserService> _logger;
        public UserService(AppDbContext context, ILogger<UserService> logger)
        {
            _context = context;
            _logger = logger;
        }
        public MiniDataGrid GetUserList(UserModel userModel)
        {
            var List = _context.USERTABLE.ToList();
            if (!string.IsNullOrWhiteSpace(userModel.USERNAME)) // 查找姓名
            {
                List = List.Where(x => x.USERNAME.Contains(userModel.USERNAME)).ToList();
            }
            if (userModel.USERSEX != 0) // 查找性别
            {
                List = List.Where(x => x.USERSEX == userModel.USERSEX).ToList();
            }
            if (!string.IsNullOrWhiteSpace(userModel.USERCARD))// 查找身份证
            {
                List = List.Where(x => x.USERCARD == userModel.USERCARD).ToList();
            }
            if (!string.IsNullOrWhiteSpace(userModel.USERPHONE))// 查找电话
            {
                List = List.Where(x => x.USERPHONE == userModel.USERPHONE).ToList();
            }
            var Items = new MiniDataGrid()
            {
                pageIndex = 1,
                pageSize = 20,
                total = List.Count,
                data = List
            };
            return Items;
        }
        public BaseResult CreateUserList(UserViewModel UserViewModel) // 添加用户数据
        {

            string time = DateTime.Now.ToString("yyyy-MM-dd");
            try
            {
                string sql = $"insert into USERTABLE values(USER_USERID_SEQ.nextval,'{UserViewModel.USERNAME}',{UserViewModel.USERSEX},to_date('{time}','yyyy-mm-dd'),'{UserViewModel.USERCARD}','{UserViewModel.USERPHONE}','{UserViewModel.PROVINCE}','{UserViewModel.CITY}','{UserViewModel.AREA}','{UserViewModel.COUNTY}',{UserViewModel.USERAGE},'{UserViewModel.PHOTOPATH}')";
                _context.Database.ExecuteSqlRaw(sql);
                if (SaveChage())
                {
                    return new BaseResult() { code = 0, msg = "添加成功" };
                }
                return new BaseResult() { code = -1, msg = "添加失败" };
            }
            catch (Exception ex)
            {
                _logger.LogInformation("出现了错误:" + ex.Message);
                return new BaseResult() { code = 0, msg = $"添加失败,{ex.Message}" };
            }
        }
        public bool SaveChage()
        {
            string sql = "commit";
            _context.USERTABLE.FromSqlRaw(sql);
            return _context.SaveChanges() >= 0;
        }
        public UserModel GetUserDetails(int UserId)
        {
            return _context.USERTABLE.Where(x => x.USERID == UserId).FirstOrDefault();
        }
        public BaseResult UpdateUserList(UserViewModel UserViewModel)
        {
            string time = DateTime.Now.ToString("yyyy-MM-dd");
            try
            {
                string SQL = $"update USERTABLE t set t.username='{UserViewModel.USERNAME}',t.usersex={UserViewModel.USERSEX},t.userbrith=to_date('{time}','yyyy-mm-dd'),t.usercard='{UserViewModel.USERCARD}',t.userphone = '{UserViewModel.USERPHONE}',t.province = '{UserViewModel.PROVINCE}',t.city = '{UserViewModel.CITY}',t.area = '{UserViewModel.AREA}',t.county = '{UserViewModel.COUNTY}',t.userage = {UserViewModel.USERAGE},t.userphoto = '{UserViewModel.PHOTOPATH}'where t.userid = {UserViewModel.USERID}";

                _context.Database.ExecuteSqlRaw(SQL);
                if (SaveChage())
                {
                    return new BaseResult() { code = 0, msg = "修改成功" };
                }
                return new BaseResult() { code = -1, msg = "修改失败" };
            }
            catch (Exception ex)
            {
                _logger.LogInformation("出现了错误:" + ex.Message);
                return new BaseResult() { code = 0, msg = $"修改失败,{ex.Message}" };
            }
        }
        public BaseResult DeleteUserList(int UserId)
        {
            try
            {
                string SQL = $"delete from USERTABLE where userid = {UserId}";
                _context.Database.ExecuteSqlRaw(SQL);
                if (SaveChage())
                {
                    return new BaseResult() { code = 0, msg = "删除成功" };
                }
                return new BaseResult() { code = -1, msg = "删除失败" };
            }
            catch (Exception ex)
            {
                _logger.LogInformation("出现了错误:" + ex.Message);
                return new BaseResult() { code = -1, msg = "删除失败" };
            }
        }
        public UserModel UserLogin(string username, string userpassword)
        {
            try
            {
                var List = _context.USERTABLE.ToList();
                UserModel userModel = List.Where(x => x.USNAME == username && x.USPASSWORD == userpassword).FirstOrDefault();
                return userModel;
            }
            catch (Exception ex)
            {
                _logger.LogInformation("出现了错误:" + ex.Message);
                return null;
            }
        }
        public List<MenuModelsViewModel> GetMenuList()
        {
            string SQL = $"select t.menuid,t.menuname,t.iconcls,t.pid,t.url,t.controller,t.methods,t.menutype,t.isenable,t.createdate,t.updatedate,t.REMARK from MENUTABLE t where t.MENUTYPE='菜单'";
            var res = _context.MENUTABLE.FromSqlRaw(SQL).ToList();
            List<MenuModelsViewModel> lis = new List<MenuModelsViewModel>();
            for (int i = 0; i < res.Count; i++)
            {
                var aa = new MenuModelsViewModel
                {
                    id = res[i].MENUID.ToString(),
                    text = res[i].MENUNAME,
                    iconCls = res[i].ICONCLS,
                    PID = res[i].PID,
                    url = res[i].URL
                };
                lis.Add(aa);
            }
            List<MenuModelsViewModel> tree = ListToTree(lis, new List<MenuModelsViewModel>(), "-1");
            return tree;
        }  // 菜单列表
        private static List<MenuModelsViewModel> ListToTree(List<MenuModelsViewModel> treeNodes, List<MenuModelsViewModel> resps, string ParentId)
        {
            resps = new List<MenuModelsViewModel>();
            List<MenuModelsViewModel> tempList = treeNodes.Where(c => c.PID.ToString() == ParentId).ToList();
            for (int i = 0; i < tempList.Count; i++)
            {
                MenuModelsViewModel node = new MenuModelsViewModel();
                node.id = tempList[i].id;
                node.text = tempList[i].text;
                node.iconCls = tempList[i].iconCls;
                node.PID = tempList[i].PID;
                node.url = tempList[i].url;
                node.children = ListToTree(treeNodes, resps, node.id.ToString());
                resps.Add(node);
            }
            return resps;
        }

        public List<MenuModels> GetUserMenuList(int ROLEID)
        {
            try
            {
                string SQL1 = $"select t.menuid,t.menuname,t.iconcls,t.pid,t.url,t.controller,t.methods,t.menutype,t.isenable,t.createdate,t.updatedate,nvl((select 1 from RMTABLE e where e.menuid = t.menuid and e.roleid = {ROLEID}),'0') REMARK from MENUTABLE t";
                List<MenuModels> aa = _context.MENUTABLE.FromSqlRaw(SQL1).ToList();
                return aa;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex.Message);
                return null;
            }
        }
        public MiniDataGrid GetUSERROLEList()
        {
            try
            {
                string SQL = "select t.roleid,t.rolename,t.iconcls,t.PID from USERROLETABLE t";
                var res = _context.USERROLETABLE.FromSqlRaw(SQL).ToList();
                var Items = new MiniDataGrid()
                {
                    pageIndex = 1,
                    pageSize = 20,
                    total = res.Count,
                    data = res
                };
                return Items;
            }
            catch(Exception ex)
            {
                _logger.LogError(ex.Message);
                return null;
            }
        }
    }
}
